let express = require("express")();
let expressStatic = require("express-static")
let mysql = require("mysql")
let sql= mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '123456',
  database : 'news',
  timezone:"08:00"
 
});
 sql.connect();
 
 //查询
express.get("/student",(request,response)=>{
	sql.query(`SELECT * FROM student`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send(data)
		}
	})
})

//删除 
express.get("/deletestudent",(request,response)=>{
	let id = request.query.id
	console.log( request.query)
	sql.query(`DELETE FROM student WHERE id=${id}`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send(data)
		}
	})
})

//添加
express.get("/joinstudent",(request,response)=>{
	let result = request.query;
	sql.query(`INSERT INTO student (name,age,sex,city,time) VALUES ("${result.name}","${result.age}","${result.sex}","${result.city}","${result.time}")`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send("success")
			console.log(`新增学生${result.name}操作成功`)
		}
	})
})

//编辑
express.get("/editstudent",(request,response)=>{
	let result = request.query;
	// console.log(result)
	sql.query(`UPDATE student SET  name="${result.name}",age="${result.age}",sex="${result.sex}",city="${result.city}",time="${result.time}" WHERE id="${result.id}"`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send("success")
			console.log(`修改学生${result.name}操作成功`)
		}
	})
})

//登录
express.get("/login",(request,response)=>{
	let result = request.query
	sql.query(`SELECT * FROM userlogin WHERE username="${result.user}" AND password="${result.password}"`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("3")
			//SQL语法错误
		}
		else{
         if(data.length === 0){
			 response.send("2")
		 }
		 else{
			 response.send(data)
		 }
		 
		}
	})
})

//注册
express.get("/register",(request,response)=>{
	let result = request.query
	console.log(result)
	sql.query(`INSERT INTO userlogin (username,password,usertype) VALUES ("${result.username}","${result.password}","${result.usertype}")`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
			//SQL语法错误
		}
		else{
		 response.send("success")
		 // console.log(data)
		 console.log(`${result.username}用户注册成功`)
		}
	})
})

express.use(expressStatic(__dirname + '/static'));
const port = 81;
express.listen(port)
console.log(`runing at ${port}`)